package com.example.triprec.utils;

import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import com.gzone.university.utils.AppUtils;

public class SqliteUtils extends SQLiteOpenHelper {

    /**
     * 用户表创建SQL
     */
    private static final String CREATE_USER_TABLE =
            "CREATE TABLE IF NOT EXISTS tb_user (" +
                    "id INTEGER PRIMARY KEY AUTOINCREMENT," +
                    "username TEXT UNIQUE NOT NULL," +
                    "password TEXT NOT NULL" +
                    ")";

    /**
     * 评论表创建SQL
     */
    private static final String CREATE_COMMENT_TABLE =
            "CREATE TABLE IF NOT EXISTS tb_comment (" +
                    "id INTEGER PRIMARY KEY AUTOINCREMENT," +
                    "content TEXT NOT NULL," +
                    "user_id INTEGER NOT NULL," +
                    "scenic_id INTEGER NOT NULL," +
                    "trip_id INTEGER NOT NULL," +
                    "create_time TEXT NOT NULL," +
                    "score REAL," +
                    "FOREIGN KEY (user_id) REFERENCES tb_user(id)" +
                    ")";

    /**
     * 收藏表创建SQL
     */
    private static final String CREATE_COLLECT_TABLE =
            "CREATE TABLE IF NOT EXISTS tb_collect (" +
                    "id INTEGER PRIMARY KEY AUTOINCREMENT," +
                    "user_id INTEGER NOT NULL," +
                    "scenic_id INTEGER NOT NULL," +
                    "create_time TEXT NOT NULL," +
                    "FOREIGN KEY (user_id) REFERENCES tb_user(id)" +
                    ")";

    /**
     * 行程表创建SQL
     */
    private static final String CREATE_TRIP_TABLE =
            "CREATE TABLE IF NOT EXISTS tb_trip (" +
                    "id INTEGER PRIMARY KEY AUTOINCREMENT," +
                    "user_id INTEGER NOT NULL," +
                    "scenic_id INTEGER NOT NULL," +
                    "trip_name TEXT NOT NULL," +
                    "start_date TEXT NOT NULL," +
                    "end_date TEXT," +
                    "create_time TEXT NOT NULL," +
                    "status INTEGER DEFAULT 0," + // 0:未出行 1:已完成
                    "FOREIGN KEY (user_id) REFERENCES tb_user(id)" +
                    ")";

    private static volatile SqliteUtils sInstance;
    private SQLiteDatabase db;

    public SqliteUtils() {
        super(AppUtils.getApplication(), "trip_rec.db", null, 8);
    }

    /**
     * 创建并获取单例
     */
    public static SqliteUtils getInstance() {
        if (sInstance == null) {
            synchronized (SqliteUtils.class) {
                if (sInstance == null) {
                    sInstance = new SqliteUtils();
                }
            }
        }
        return sInstance;
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        this.db = db;
        // 创建用户表
        db.execSQL(CREATE_USER_TABLE);
        // 创建评论表
        db.execSQL(CREATE_COMMENT_TABLE);
        // 创建收藏表
        db.execSQL(CREATE_COLLECT_TABLE);
        // 创建行程表
        db.execSQL(CREATE_TRIP_TABLE);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        this.db = db;
        //删除表
        db.execSQL("DROP TABLE IF EXISTS tb_user");
        db.execSQL("DROP TABLE IF EXISTS tb_comment");
        db.execSQL("DROP TABLE IF EXISTS tb_collect");
        db.execSQL("DROP TABLE IF EXISTS tb_trip");

        //重新创建表
        onCreate(db);
    }

    @Override
    public void onOpen(SQLiteDatabase db) {
        super.onOpen(db);
        this.db = db;
    }

    @Override
    public void onConfigure(SQLiteDatabase db) {
        super.onConfigure(db);
        this.db = db;
    }

    @Override
    public void onDowngrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        this.db = db;
        // 降级策略，可以选择与onUpgrade相同的逻辑
        db.execSQL("DROP TABLE IF EXISTS tb_user");
        db.execSQL("DROP TABLE IF EXISTS tb_comment");
        db.execSQL("DROP TABLE IF EXISTS tb_collect");
        db.execSQL("DROP TABLE IF EXISTS tb_trip");
        onCreate(db);
    }

    @Override
    public SQLiteDatabase getWritableDatabase() {
        if (db == null || !db.isOpen()) {
            db = super.getWritableDatabase();
        }
        return db;
    }

    @Override
    public SQLiteDatabase getReadableDatabase() {
        if (db == null || !db.isOpen()) {
            db = super.getReadableDatabase();
        }
        return db;
    }
}
